from sqlite3 import dbapi2 as sqlite
import os
import sys


class lotto_db():

    def __init__(self, winnums=None, dbname="lotto-hist"):
        if (winnums is None):
            #flist = ["Date", "First", "Second",
                     #"Third", "Fourth", "Fifth", "Powerball"]
    ##        print ("initialize database connection")
            self.conn = sqlite.connect(dbname)
            self.conn.row_factory = sqlite.Row
            self.curs = self.conn.cursor()
    #        print self.curs
            self.conn.text_factory = sqlite.OptimizedUnicode
            self.curs.execute(
                "SELECT name FROM sqlite_master WHERE type='table';")
        else:
            # don't open the database,
            # create a new one from the filename mentioned
#check for file exists
            if os.path.exists(winnums):
                f = open(winnums)
                lines = f.readlines().split("\n")
                f.close()
            else:
                print ("Input file not found!\nExiting!")
                sys.exit()
            lines.reverse()
            #open a new database connection
            dbname = winnums + ".db"
            self.conn = sqlite.open(dbname)
            self.conn.row_factory = sqlite.Row
            self.curs = self.conn.cursor()
            #read the lines from the reversed file
            #into the database
            #create the initial tables history and dates
            self.conn.execute(
                'CREATE TABLE history ("date" TEXT NOT NULL, "first"'
                ' INTEGER NOT NULL, "second" INTEGER NOT NULL, "third"'
                ' INTEGER NOT NULL, "fourth" INTEGER NOT NULL, "fifth"'
                ' INTEGER NOT NULL, "powerball" INTEGER NOT NULL)')
            self.conn.execute('CREATE TABLE dates '
            '("pkindex" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'
            '"date" TEXT)')
            for line in lines:
                fields = line.split()
                date = fields[1]
                first = fields[2]
                second = fields[3]
                third = fields[4]
                fourth = fields[5]
                fifth = fields[6]
                powerball = fields[7]
                self.conn.execute(
                    "INSERT INTO history VALUES (?, ?, ?, ?, ?, ?, ?)"
                [date, first, second, third, fourth, fifth, powerball])
                self.conn.execute("INSERT INTO dates VALUES (?)" [date])

    def create_history(self, number):
        print ("creating history")
        new_table = "whiteball_" + str(number)
        self.conn.execute('DROP TABLE IF EXISTS %s' % (new_table))
        self.conn.execute(
            'CREATE TABLE  %s (dates_drawn  INT) ' % (new_table))
        self.conn.commit()
        self.curs.execute(
            "SELECT * FROM history WHERE first=? OR second=? OR third=?"
            " OR fourth=? OR fifth=? ORDER BY date", (
            number, number, number, number, number))
        self.conn.text_factory = str
        rows = self.curs.fetchall()

        for row in rows:
            self.curs.execute(
                'select pkindex from dates where date = ?', [row[0]])
            date_index = self.curs.fetchone()
            # print "Sorting number: " + str(number) + " at date: " +
            # str(date_index[0])
            self.curs.execute('INSERT INTO %s VALUES (?)' %
                              (new_table), [date_index[0]])
            self.conn.commit()

    def create_skips(self, number):
        print ("creating skips")
        new_table = "whiteball_" + str(number)
        skip_table = "skips_" + str(number)
        self.conn.execute('drop table if exists %s' % (skip_table))
        self.conn.execute(
            'create table %s(skip_number int, skip_count int)' % (skip_table))
        self.curs.execute('select * from %s' % (new_table))
        rows = self.curs.fetchall()
        previous_hit = 0
        for row in rows:
            next_hit = row[0]
            skip_count = next_hit - previous_hit
            #50 through 59 were added later so skip the first skip
            if skip_count > 99:
                return
            # print "skip count " + str(skip_count) + " for ball " +
            # str(number)
            self.curs.execute(
                'select skip_count from %s where skip_number = ?' %
                skip_table, [skip_count])
            old_skip_count = 1
            skip_count_row = self.curs.fetchone()
            if skip_count_row is None:
                self.curs.execute('insert into %s values(?,?)' %
                                  (skip_table), [skip_count, old_skip_count])
            else:
                # print skip_count_row
                old_skip_count = skip_count_row[0]
                old_skip_count += 1
                self.curs.execute(
                    'update  %s set skip_count=? where skip_number=? '
                    % (skip_table), [old_skip_count, skip_count])
            previous_hit = next_hit
            self.conn.commit()

    # find the last draw and draw count
    def get_last_row(self, table_name):
        # curs = self.conn.cursor()
        self.curs.execute("Select * from %s" % table_name)
        rows = self.curs.fetchall()
        index = 0
        for row in rows:
            last_row = row
            index += 1
        return last_row, index

    # return date as a string
    def get_draw_date(self, date_index):
        self.curs.execute(
            "Select date from dates where pkindex = ?", [date_index])
        row = self.curs.fetchone()
        return row[0]

    # return date as an index
    def get_date_index(self, date_string):
        self.curs.execute(
            "Select pkindex from dates where date = ?", [date_string])
        row = self.curs.fetchone()
        return row[1]

    # get the best skip count
    def get_highest_skip(self, number):
        skip_table = "skips_" + str(number)
        self.curs.execute(
            "Select  * from " + skip_table + " order by skip_count desc")
        best_skip = self.curs.fetchone()
        # print best_skip
        return best_skip[0], best_skip[1]

    # return 3 highest skip counts
    def get_highest_skips(self, number):
        skip_table = "skips_" + str(number)
        self.curs.execute(
            "Select  * from " + skip_table + " order by skip_count desc")
        best_skip = self.curs.fetchall()
        first_skip = best_skip[0]
        second_skip = best_skip[1]
        third_skip = best_skip[2]
        return first_skip[0], second_skip[0], third_skip[0]

    # get current skip count
    def get_current_skip(self, number):
        last_draw = self.get_last_row("dates")
        last_draw_index = last_draw[0]
        number_table = "whiteball_" + str(number)
        number_draw = self.get_last_row(number_table)
        number_draw_index = number_draw[0]
        return last_draw_index[0] - number_draw_index[0]

    # update database
    def update_database(
        self, date, first, second, third, fourth, fifth, powerball):

        self.curs.execute(
            'INSERT INTO history'
            '(date, first, second, third, fourth, fifth, powerball)'
            ' values("%s", %s, %s, %s, %s, %s, %s)' %
            (date, first, second, third, fourth, fifth, powerball))
        last_date = self.get_last_row("dates")
        new_index = last_date[0][0] + 1
        self.curs.execute(
            'insert into dates values (%s, "%s")' % (new_index, date))
        for number in [first, second, third, fourth, fifth]:
            number_table = "whiteball_" + str(number)
            self.curs.execute(
                'INSERT INTO ' + number_table + ' VALUES (%s)' % (new_index))
            number_skip = self.get_current_skip(number)
            skip_table = "skips_" + str(number)
            self.curs.execute(
                'select skip_count from %s where skip_number = %s'
                 % (skip_table, number_skip))
            old_skip_count = 1
            skip_count_row = self.curs.fetchone()
            if skip_count_row is None:
                self.curs.execute('insert into %s values(%s, %s)' %
                                  (skip_table, number_skip, old_skip_count))
            else:
                # print skip_count_row
                old_skip_count = skip_count_row[0]
                old_skip_count += 1
                self.curs.execute(
                    'update  %s set skip_count=%s where skip_number=%s '
                    % (skip_table, old_skip_count, number_skip))
        self.conn.commit()

    def get_history_draw(self, date):
        self.curs.execute("SELECT * FROM history WHERE date=?", [date])
        draw_data = self.curs.fetchone()
        return draw_data
#
# get all the rows of a table
#

    def get_table(self, table_name):
        # return a list of table rows
        self.curs.execute("Select * from %s" % table_name)
        table = self.curs.fetchall()
        return table
#
# get all of the skips from a number
#

    def get_skip(self, whiteball):
        table = "skips_" + str(whiteball)
        all_the_skips = self.get_table(table)
        return all_the_skips